﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Net.NetworkInformation;
using System.Text;

namespace iSet
{
    public class DB
    {
        private static string connstring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=iSet.db";

        #region Setting

        public static DataTable GetSettingList()
        {
            string sql = @"select id,SettingName from IPList order by id asc";

            DataTable dt = new QT.DBO.OleDBHelper(connstring).DataTable(sql);
            return dt;
        }

        public static string GetSettingID(string Nid)
        {
            string sql = @"select Sid from Setting where Nid='" + Nid + "'";

            DataTable dt = new QT.DBO.OleDBHelper(connstring).DataTable(sql);
            if (dt.Rows.Count != 1) return "0";
            else return dt.Rows[0]["Sid"].ToString();
        }

        public static bool AddSetting(string Nid, string Sid)
        {
            string sql = @"insert into Setting(Sid,Nid) values(" + Sid + ",'" + Nid + "')";
            if (new QT.DBO.OleDBHelper(connstring).ExecuteNonQuery(sql) > -1) return true;
            else return false;
        }

        public static bool UpdateSetting(string Nid, string Sid)
        {
            string sql = @"update Setting set Sid=" + Sid + " where Nid='" + Nid + "'";
            if (new QT.DBO.OleDBHelper(connstring).ExecuteNonQuery(sql) > -1) return true;
            else return false;
        }

        #endregion

        #region IPSetting

        public static DataTable GetIPSetting(string sid)
        {
            string sql = @"select isdhcp,autodns,ip,mask,gateway,dns1,dns2 from IPList where id=" + sid + "";

            DataTable dt = new QT.DBO.OleDBHelper(connstring).DataTable(sql);
            return dt;
        }

        public static bool CheckIPSettingName(string SettingName,string Sid)
        {
            string sql = @"select SettingName from IPList where settingname='" + SettingName + "' and id not in(" + Sid + ")";

            DataTable dt = new QT.DBO.OleDBHelper(connstring).DataTable(sql);
            if(dt.Rows.Count>0) return true;
            else return false;
        }

        public static bool AddIPSetting(string SettingName, bool IsDhcp, string IP, string Mask, string GateWay, bool AutoDns, string Dns1, string Dns2)
        {
            string sql = @"insert into IPList(SettingName,IsDhcp,IP,Mask,GateWay,AutoDns,Dns1,Dns2) values('" + SettingName + "'," + IsDhcp + ",'" + IP + "','" + Mask + "','" + GateWay + "'," + AutoDns + ",'" + Dns1 + "','" + Dns2 + "')";

            if (new QT.DBO.OleDBHelper(connstring).ExecuteNonQuery(sql) > -1) return true;
            else return false;
        }

        public static bool UpdateIPSetting(string Sid, string SettingName, bool IsDhcp, string IP, string Mask, string GateWay, bool AutoDns, string Dns1, string Dns2)
        {
            string sql = @"update IPList set SettingName='" + SettingName + "',IsDhcp=" + IsDhcp + ",IP='" + IP + "',Mask='" + Mask + "',GateWay='" + GateWay + "',AutoDns=" + AutoDns + ",Dns1='" + Dns1 + "',Dns2='" + Dns2 + "' where id=" + Sid;

            if (new QT.DBO.OleDBHelper(connstring).ExecuteNonQuery(sql) > -1) return true;
            else return false;
        }

        public static bool DeleteIPSetting(string Sid)
        {
            string sql = @"Delete from IPList where id=" + Sid;

            if (new QT.DBO.OleDBHelper(connstring).ExecuteNonQuery(sql) > -1)
            {
                sql = @"Delete from Setting where sid=" + Sid ;

                if (new QT.DBO.OleDBHelper(connstring).ExecuteNonQuery(sql) > -1) return true;
                else return false;
            }

            return false;
        }

        #endregion

        #region NetCard

        public static DataTable GetNetCardList()
        {
            DataTable dt = new DataTable();
            DataColumn dc1 = dt.Columns.Add("Nid", Type.GetType("System.String"));
            dc1.AllowDBNull = false;
            DataColumn dc2 = dt.Columns.Add("Des", Type.GetType("System.String"));
            dc2.AllowDBNull = false;
            NetworkInterface[] NetworkInterfaces = NetworkInterface.GetAllNetworkInterfaces();
            foreach (NetworkInterface NetworkIntf in NetworkInterfaces)
            {
                if (NetworkIntf.NetworkInterfaceType == NetworkInterfaceType.Ethernet || NetworkIntf.NetworkInterfaceType == NetworkInterfaceType.Wireless80211)
                {
                    DataRow dr = dt.NewRow();
                    dr["Nid"] = NetworkIntf.GetPhysicalAddress().ToString();
                    dr["Des"] = NetworkIntf.Name;
                    dt.Rows.Add(dr);
                }
            }

            return dt;
        }

        #endregion

        #region AdminUser

        public static DataTable GetUser()
        {
            string sql = @"select username,pass from Administrator";

            return new QT.DBO.OleDBHelper(connstring).DataTable(sql);
        }

        public static int UpdateUser(string username, string pass)
        {
            string sql = @"select id from Administrator";
            DataTable dt = new QT.DBO.OleDBHelper(connstring).DataTable(sql);
            if (dt.Rows.Count > 0)
            {
                sql = "update Administrator set username='" + username + "',pass='" + pass + "'";
            }
            else
            {
                sql = "insert into Administrator(username,pass) values('" + username + "','" + pass + "')";
            }

            return new QT.DBO.OleDBHelper(connstring).ExecuteNonQuery(sql);
        }

        #endregion
    }
}
